/**
 * 
 */
package com.clh.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.clh.dao.OwnerDao;
import com.clh.entity.Owner;
import com.clh.util.DBUtil;



/**
 * @Description: 业主信息dao实现类
 * @author 张经伟
 * @date 2022年5月25日 下午5:47:58
 * @version v1.0
 */

public class OwnerDaoImpl implements OwnerDao{


	/**
	 * 分页
	 */
	@Override
	public List<Owner> findByPage(Owner owner, int pageNum, int pageSize) {
		List<Owner> list = new ArrayList<Owner>();
		
		StringBuffer sql = new StringBuffer();
		sql.append("select")
			.append(" room_no,")
			.append(" owner_name,")
			.append(" owner_id,")
			.append(" owner_phoneNumber,")
			.append(" check_in_time,")
			.append(" owner_image,")
			.append(" owner_password,")
			.append(" room_area")
			.append(" from owner");
		if(true) {
			sql.append(" where 1 = 1");
		}
		
		if(owner.getOwnerName() !=null && !"".equals(owner.getOwnerName())) {
			sql.append(" and owner_name like '" + owner.getOwnerName() + "'");
		}
		
		if(owner.getRoomNo() !=null && !"".equals(owner.getRoomNo())) {
			sql.append(" and room_no = " + owner.getRoomNo());
		}
		
		if(owner.getOwnerPhoneNumber() !=null && !"".equals(owner.getOwnerPhoneNumber())) {
			sql.append(" and owner_phoneNumber = " + owner.getOwnerPhoneNumber());
		}
		
		sql.append(" limit " + (pageNum-1)*pageSize + "," +pageSize);//分页
		
		ResultSet rs = DBUtil.query(sql.toString());
		
		try {
			while (rs.next()) {
				String roomNo = rs.getString("room_no");
				String ownerName = rs.getString("owner_name");
				String ownerId = rs.getString("owner_id");
				String ownerPhoneNumber	 = rs.getString("owner_phoneNumber");			
				Date checkInTime = rs.getDate("check_in_time");
				String ownerImage = rs.getString("owner_image");
				String ownerPassword = rs.getString("owner_password");
				BigDecimal roomArea = rs.getBigDecimal("room_area");
				
				Owner owner1 = new Owner();
				owner1.setRoomNo(roomNo);
				owner1.setOwnerName(ownerName);
				owner1.setOwnerId(ownerId);
				owner1.setOwnerPhoneNumber(ownerPhoneNumber);
				owner1.setCheckInTime(checkInTime);
				owner1.setOwnerImage(ownerImage);
				owner1.setOwnerPassword(ownerPassword);
				owner1.setRoomArea(roomArea);
				list.add(owner1);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return list;
	}

	
	/**
	 * 结果统计
	 */
	@Override
	public int countByCondition(Owner owner) {
		int n =0;
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" count(*)")
		.append(" from owner ");
		
		if(true) {
			sql.append(" where 1 = 1");
		}
		
		if(owner.getOwnerName() !=null && !"".equals(owner.getOwnerName())) {
			sql.append(" and owner_name like '" + owner.getOwnerName() + "'");
		}
		
		if(owner.getRoomNo() !=null && !"".equals(owner.getRoomNo())) {
			sql.append(" and room_no like '" + owner.getRoomNo() + "'");
		}
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				//获取每一列的数据
				n = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
			
		return n;
	}

	
	/**
	 * 删除
	 */
	@Override
	public int deleteById(String roomNo) {
		StringBuilder sql = new StringBuilder();
		sql.append("delete from owner where room_no=?");
		int n = DBUtil.update(sql.toString(),roomNo);
		return n;
	}

	
	/**
	 * 添加
	 */
	@Override
	public int insert(Owner owner) {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into owner(room_no,owner_name,owner_id,owner_phoneNumber,check_in_time,owner_image,owner_password,room_area)")
		.append(" values (?,?,?,?,?,?,?,?)");
		int n = DBUtil.update(sql.toString(),
				owner.getRoomNo(),owner.getOwnerName(),owner.getOwnerId(),owner.getOwnerPhoneNumber(),
				owner.getCheckInTime(),owner.getOwnerImage(),owner.getOwnerPassword(),owner.getRoomArea());
				
		return n;
	}


	/**
	 * 用于业主登录，使用手机号码和密码
	 */
	@Override
	public Owner findByPhoneNumberAndPassword(String phoneNumber, String password) {
		Owner owner=null;
		StringBuffer sql=new StringBuffer();
		sql.append("select * ")
		.append("from owner")
		.append(" where owner_phoneNumber=?")
		.append(" and owner_password=?");
		ResultSet rs=DBUtil.query(sql.toString(), phoneNumber,password);
		System.out.println("验证SQL语句"+sql.toString());
		try {
			while(rs.next()) {
				String roomNo=rs.getString("room_no");
				String ownerName=rs.getString("owner_name");
				owner=new Owner();
				owner.setRoomNo(roomNo);
				owner.setOwnerName(ownerName);
				owner.setOwnerPhoneNumber(phoneNumber);
				owner.setOwnerPassword(password);
				
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return owner;
	}


	/**
	 * 修改
	 */
	@Override
	public int update(Owner owner) {
		StringBuilder sql = new StringBuilder();
		sql.append("update owner set room_no=?,")
		.append(" owner_name=?,")
		.append(" owner_id=?,")
		.append(" owner_phoneNumber=?,")
		.append(" check_in_time=?,");
		if(owner.getOwnerImage() != null) {
		sql.append(" owner_image=?,");
		}
		sql.append(" owner_password=?,")
		.append(" room_area=?")
		.append(" where room_no=?");
		
		int n = 0;
		System.out.println("dao1"+owner.getOwnerPhoneNumber());
		if(owner.getOwnerImage() == null) {
			n = DBUtil.update(sql.toString(), 
					owner.getRoomNo(),owner.getOwnerName(),owner.getOwnerId(),owner.getOwnerPhoneNumber(),
					owner.getCheckInTime(),owner.getOwnerPassword(),owner.getRoomArea(),owner.getRoomNo());
		}else {
			n = DBUtil.update(sql.toString(), 
					owner.getRoomNo(),owner.getOwnerName(),owner.getOwnerId(),owner.getOwnerPhoneNumber(),
					owner.getCheckInTime(),owner.getOwnerImage(),owner.getOwnerPassword(),owner.getRoomArea(),owner.getRoomNo());
		}
		return n;
	}


	@Override
	public Owner findById(String roomNo) {
		Owner owner = null;
		StringBuilder sql = new StringBuilder();
		
		sql.append("select")
		.append(" room_no,")
		.append(" owner_name,")
		.append(" owner_id,")
		.append(" owner_phoneNumber,")
		.append(" check_in_time,")
		.append(" owner_image,")
		.append(" owner_password,")
		.append(" room_area")
		.append(" from owner")
		.append(" where room_no = ?");
		
		ResultSet rs = DBUtil.query(sql.toString(),roomNo);
		try {
			while (rs.next()) {
				
				String ownerName = rs.getString("owner_name");
				String ownerId = rs.getString("owner_id");
				String ownerPhoneNumber	 = rs.getString("owner_phoneNumber");			
				Date checkInTime = rs.getDate("check_in_time");
				String ownerImage = rs.getString("owner_image");
				String ownerPassword = rs.getString("owner_password");
				BigDecimal roomArea = rs.getBigDecimal("room_area");
				
				Owner owner1 = new Owner();
				owner1.setRoomNo(roomNo);
				owner1.setOwnerName(ownerName);
				owner1.setOwnerId(ownerId);
				owner1.setOwnerPhoneNumber(ownerPhoneNumber);
				owner1.setCheckInTime(checkInTime);
				owner1.setOwnerImage(ownerImage);
				owner1.setOwnerPassword(ownerPassword);
				owner1.setRoomArea(roomArea);
				return owner1;
				
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return owner;
	}


	@Override
	public List<Owner> findByPhone(String ownerPhoneNumber) {
		Owner owner = null;
		List<Owner> list = new ArrayList<Owner>();
		StringBuilder sql = new StringBuilder();
		sql.append("select")
		.append(" room_no,")
		.append(" owner_name,")
		.append(" owner_id,")
		.append(" owner_phoneNumber,")
		.append(" check_in_time,")
		.append(" owner_image,")
		.append(" owner_password,")
		.append(" room_area")
		.append(" from owner")
		.append(" where owner_phoneNumber=? ");
		
		ResultSet rs = DBUtil.query(sql.toString(),ownerPhoneNumber);
		
		try {
			while (rs.next()) {
				String roomNo = rs.getString("room_no");
				String ownerName = rs.getString("owner_name");
				String ownerId = rs.getString("owner_id");
							
				Date checkInTime = rs.getDate("check_in_time");
				String ownerImage = rs.getString("owner_image");
				String ownerPassword = rs.getString("owner_password");
				BigDecimal roomArea = rs.getBigDecimal("room_area");
				
				Owner owner1 = new Owner();
				owner1.setRoomNo(roomNo);
				owner1.setOwnerName(ownerName);
				owner1.setOwnerId(ownerId);
				owner1.setOwnerPhoneNumber(ownerPhoneNumber);
				owner1.setCheckInTime(checkInTime);
				owner1.setOwnerImage(ownerImage);
				owner1.setOwnerPassword(ownerPassword);
				owner1.setRoomArea(roomArea);
				list.add(owner1);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return list;
	}


	@Override
	public List<Owner> getAllOwners() {
		List<Owner> list = new ArrayList<Owner>();
		StringBuilder sql = new StringBuilder();
		sql.append("select ")
		.append(" room_no,")
		.append("room_area")
		.append(" from owner");
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				Owner owner = new Owner();
				owner.setRoomNo(rs.getString("room_no"));
				owner.setRoomArea(rs.getBigDecimal("room_area"));
				list.add(owner);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
		return list;
	}



}
